<?php
	//DB Connection File
	require_once('DBConnection5.Class.php');
	require_once("class.phpmailer.php");
	
	//DB Connections Variables
	$SERVER = "localhost";    
	$DBASE = "iwn"; 		
	$USERNAME = "root";		
	$PASSWORD = "";			
	
	$DBOBJ = new myclass($SERVER, $DBASE, $USERNAME, $PASSWORD);

	$weekPastDate = date('Y-m-d',strtotime("-7 day")); //Date before 7 days form current date
	#$weekPastDate = '2013-04-01';
	$currentdate = date('Y-m-d'); //Current Date
	
	
	$settingsSql = "SELECT setting_value FROM settings WHERE setting_name = 'no_reply_email'";
	$settings = $DBOBJ->select($settingsSql);
	
	/*Total Active Jobs POSTED withing last seven days*/
	$activeJobSql = "SELECT j.id as job_id,j.title,j.trade_id ,a.`area`,a.longitude,a.latitude,t.trade
					FROM jobs j
					JOIN trades t ON j.trade_id = t.id 
					JOIN areas a ON j.area_id = a.id 
					JOIN addresses adr ON j.address_id = adr.id 
					WHERE j.status = 'Open' AND j.job_active = '1' AND j.deleted <> '1' 
					AND j.date_added BETWEEN '".$weekPastDate."' AND '".$currentdate."'
					ORDER BY j.date_added";

	$aciveJobs = $DBOBJ->select($activeJobSql);
	
	
	foreach($aciveJobs as $_jobs) {
		$jobsArray[$_jobs['job_id']] = $_jobs;
	}
	
	$tradesIdArray = Array();
	foreach($aciveJobs as $_val) {
		$tradesIdArray[$_val['trade_id']] = $_val['trade_id'];
	}
	
	$tradeIds = '';
	if ( count($tradesIdArray) > 0 ) {
		$tradeIds = implode(",",$tradesIdArray);
	}
	
	if ( $tradeIds != '' ) {
			/*All tradesman to whom mail should be sent */
			$tradesmanSql = "SELECT tt.tradesman_id,t.id,t.user_id,t.company_name,t.address_id,tt.trade_id,s.name,s.radius,u.firstname,u.surname,u.email,a.longitude,a.latitude
							FROM  tradesman_to_trade tt
							JOIN tradesman t ON t.id = tt.tradesman_id
							JOIN subscriptions s ON t.subscription_id = s.id
							JOIN users u ON t.user_id = u.id 
							JOIN addresses a ON t.address_id = a.id
							WHERE tt.trade_id  IN (".$tradeIds.") AND t.active = 1 AND u.banned = 0 AND u.newsletter = 1 AND u.tradesman = 1
							GROUP BY  tt.tradesman_id";
			$tradesManArray = $DBOBJ->select($tradesmanSql);
			$newsletter_content = '';
			foreach($tradesManArray as $_tradeMan) {
				foreach($jobsArray as $_job) {
					if ($_tradeMan['trade_id'] == $_job['trade_id']) {
						$distance = '';
						$distance = getDistanceBetweenPoints($_tradeMan['latitude'],$_tradeMan['longitude'], $_job['latitude'],$_job['longitude']);
						//Condition to send mail as per subscription area
						if ( $distance <= $_tradeMan['radius']  ) {
								//Mail Logic Over Here
								//Job Links Building
								$newsletter_content = '';
								$link = build_job_link($_job['trade'],$_job['area'],$_job['job_id']);
								$newsletter_content.='<a href="http://www.iwantatradesman.co.uk'.$link .'" style="color: #002D4E; text-decoration: none;"><b>'.$_job['title'].'</b></a> - '.$_job['area'].'<br />';
								
								$html_body = '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> 
												<html>
												<head>
												</head>
												<body style="background-color: #eaeaea;">

														<table width="600" cellpadding="0" cellspacing="0" align="center" style="background-color: #eaeaea;">
														<tr><td valign="bottom" style="font-weight: bold; font-family: arial, sans-serif; font-size: 14px;"><a href="http://www.iwantatradesman.co.uk"><img src="http://www.iwantatradesman.co.uk/images/logo.gif" alt="I Want A Tradesman" border="0"></a>
														<br><br><a href="http://www.iwantatradesman.co.uk" style="color: #002D4E; text-decoration: none;">Home</a> &nbsp;|&nbsp; <a href="http://www.iwantatradesman.co.uk/post-a-job" style="color: #002D4E; text-decoration: none;">Post a job</a> &nbsp;|&nbsp; <a href="http://www.iwantatradesman.co.uk/tradesmen" style="color: #002D4E; text-decoration: none;">Tradesmen</a> &nbsp;|&nbsp; <a href="http://www.iwantatradesman.co.uk/view-jobs" style="color: #002D4E; text-decoration: none;">Jobs</a> &nbsp;|&nbsp; <a href="http://www.iwantatradesman.co.uk/help" style="color: #002D4E; text-decoration: none;">Help</a> &nbsp;|&nbsp; <a href="http://www.iwantatradesman.co.uk/blog" style="color: #002D4E; text-decoration: none;">Blog</a>
														</td></tr>
														<tr><td height="20"></td></tr>
														<tr><td style="color: #666666; font-size: 12px; font-family: arial, sans-serif;" align="left">Dear '.$_tradeMan['company_name'].'<br /><br />Welcome to this weeks job alert newsletter. Below you will find all jobs available to quote within a '.$_tradeMan['radius'].' mile radius for your trades.<br /><br />
														'.$newsletter_content.'
														<br><br>
														Best regards<br><br>
														<strong>The I Want A Tradesman team</strong><br><br>
														<a href="http://www.iwantatradesman.co.uk" style="color: #666666;">www.iwantatradesman.co.uk</a></td></tr>
														<tr><td height="20"></td></tr>
														<tr><td style="color: #666666; font-size: 11px; font-family: arial, sans-serif;" align="left">You have received this email because you have requested to receive updates and news from <a href="http://www.iwantatradesman.co.uk" style="color: #666666;">iwantatradesman.co.uk</a>.<br><a href="http://www.iwantatradesman.co.uk/notification-settings" style="color: #666666;">Click here to unsubscribe.</a></td></tr>
														<tr><td height="20"></td></tr>
														<tr><td style="color: #666666; font-size: 11px; font-family: arial, sans-serif;" align="left">This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited.</td></tr>
														<tr><td height="20"></td></tr>
														</table>

												</body>
												</html>';
								
						$mail = new PHPMailer();
						$mail->IsSMTP(); // telling the class to use SMTP
						$mail->isHTML(true);
						$mail->Host = "localhost"; // SMTP server
						$mail->From = $settings['setting_value'];
						$mail->FromName = "I Want A Tradesman";

						$mail->AddAddress($rs['email']);

						$mail->Subject = "Job alerts from IWantATradesman";
						$mail->Body = $html_body;

						$mail->Send();
						$mail->ClearAddresses();
								
						}
					}
				}
			}
	}
	
	
/* This Function is used to calculate the distance between towo co-ordinate of latitude and longitude */
    function getDistanceBetweenPoints($latitude1, $longitude1, $latitude2, $longitude2, $unit = 'Mi') {
        $theta = $longitude1 - $longitude2;
        $distance = (sin(deg2rad($latitude1)) * sin(deg2rad($latitude2))) + (cos(deg2rad($latitude1)) * cos(deg2rad($latitude2)) * cos(deg2rad($theta)));
        $distance = acos($distance);
        $distance = rad2deg($distance);
        $distance = $distance * 60 * 1.1515;
        switch ($unit) {
            case 'Mi':
                break;
            case 'Km' :
                $distance = $distance * 1.609344;
                break;
        }
        $distance = round($distance, 2);
		return $distance;
    }	
	
	/*Function Copied from the buildLinks.php*/	
	function clean_file_name($Raw){
		$Raw = trim(strtolower($Raw));
		$RemoveChars  = array( "([\40])" , "([^a-zA-Z0-9-])", "(-{2,})" );
		$ReplaceWith = array("-", "", "-");
		return preg_replace($RemoveChars, $ReplaceWith, $Raw);
	} 

	/*Function Copied from the buildLinks.php*/	
	function build_job_link($trade,$area,$id){

	$link = '/job/'.clean_file_name($trade).'/'.clean_file_name($area).'/'.$id;
	
	return $link;

	}
	
	
exit;
?>